<?php

class Gateway_Model extends Model {

    public function __construct() {
        parent::__construct();
    }

    public function smsList() {
        return $this->db->select('SELECT *  FROM `smsd`.`inbox` WHERE `read` = :read', array(':read' => '0'));
    }

    public function inboxStatus() {
        return $this->db->select('SELECT `RecipientID`, COUNT(`ID`) as count FROM `smsd`.`inbox`  GROUP BY `RecipientID`');
    }

    public function outboxStatus() {
        return $this->db->select('SELECT `SenderID`, COUNT(`ID`) as count FROM `smsd`.`outbox` GROUP BY `SenderID`');
    }

    public function sentitemsStatus() {
        return $this->db->select('SELECT `SenderID`, COUNT(`ID`) as count FROM `smsd`.`sentitems` GROUP BY `SenderID`');
    }

    public function respuestaStatus() {
        return $this->db->select("SELECT
`RecipientID`,
SUM(CASE `respuesta` WHEN 'POSITIVE' THEN 1 WHEN 'NEUTRAL' THEN 1 END) as POSITIVE,
SUM(CASE `respuesta` WHEN 'NEGATIVE' THEN 1 END) as NEGATIVE
FROM `smsd`.`inbox`
GROUP BY `RecipientID`");
    }

    public function test() {
        return $this->db->select('SELECT `cod_municipio`,`municipio`, `dispositivo`FROM `cne` GROUP BY `cod_municipio`');
    }

    public function verificarTelefono($numero) {
        $data = $this->db->select('SELECT count(cedula) as cantidad FROM `cne` WHERE `telefono`= :telefono or `telefono2`= :telefono', array(':telefono' => $numero));
//         print_r($data);
        if ($data[0]['cantidad'] > 0) {
            return true;
        } else {
            return FALSE;
        }
    }

    public function sendSmsResponsable() {
        return $this->db->select("SELECT `cedula`, `nombre`, `telefono`, `telefono2`, `dispositivo`
FROM `cne`
WHERE `patrulla1x10_responsable` = 1
AND (
`telefono` LIKE '0416%'
OR `telefono` LIKE '0426%'
OR `telefono` LIKE '0414%'
OR `telefono` LIKE '0424%'
OR `telefono` LIKE '0412%'
)");
    }

}